﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Configuration;
using System.Data;
using Model.BusinessLogic.FindEntityInfo;
using DBUtility;
using System.Data.SqlClient;
using MyExection;
using Model.BusinessLogic.EntityInfo;
using Util;

namespace BusinessLogicDAL
{
    public class BuMenChanPinRenWuDAL
    {
        private readonly string conStr = ConfigurationManager.AppSettings["ERPDataConnStr"];

        public BuMenChanPinRenWuDAL()
        {
        }

        /// <summary>
        /// 创建人：
        /// 创建时间：2010-7-12
        /// 功能介绍：查询DataTable信息
        /// </summary>
        /// <param name="FindEntity  findEntity"></param>
        /// <returns></returns>
        public DataTable SearchByFindEntity(FindBuMenChanPinRenWuEntity findEntity)
        {
            try
            {
                string strSql = GetSearchSQLStrings();
                SqlParameter[] sqlParas = GetSearchParas(findEntity);
                DataTable tempDT = DBSQLHelper.GetDataTable(conStr, CommandType.Text, strSql, sqlParas);
                return tempDT;
            }
            catch (Exception e)
            {
                throw new CustomException(e.Message, "ObjectDAL.SearchByFindEntity(FindEntity  findEntity)", "查询DataTable信息");
            }
        }

        /// <summary>
        /// 创建人：
        /// 创建时间:2010-7-12
        /// 功能简介:返回参数赋值组
        /// </summary>
        /// <param name="FindEntity  findEntity"></param>
        /// <returns></returns>
        private SqlParameter[] GetSearchParas(FindBuMenChanPinRenWuEntity findEntity)
        {
            SqlParameter[] sqlParameters = { 
                    new SqlParameter("@BM_ID",findEntity.Cardcode),
                    new SqlParameter("@BMCP_RenWuNianFen",'%' + findEntity.BMCP_RenWuNianFen + '%'),
                 };
            return sqlParameters;
        }

        /// <summary>
        /// 创建人：
        /// 创建时间:2010-7-12
        /// 功能简介:返回Sql语句
        /// </summary>
        /// <returns></returns>
        private string GetSearchSQLStrings()
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(" SELECT TB_BuMenChanPinRenWu.*");
            strSql.Append(" FROM TB_BuMenChanPinRenWu");
            
            strSql.Append(" WHERE TB_BuMenChanPinRenWu.BM_ID = @BM_ID");
            strSql.Append(" AND BMCP_RenWuNianFen LIKE @BMCP_RenWuNianFen");
            return strSql.ToString();
        }
        /// <summary>
        /// 创建人：
        /// 创建时间：2010-7-12
        /// 功能介绍：添加BuMenChanPinRenWuEntity
        /// </summary>
        /// <param name="entityInfo"></param>
        /// <returns></returns>
        public bool Add(SqlTransaction tran, BuMenChanPinRenWuEntity entityInfo)
        {
            string strSql = GetAddSQLStrings();
            SqlParameter[] sqlParas = GetBuMenChanPinRenWuEntityAddParas(entityInfo);
            try
            {
                int result = DBSQLHelper.ExecuteNonQuery(tran, CommandType.Text, strSql, sqlParas);
                if (result > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (Exception e)
            {
                throw new CustomException(e.Message, "BuMenChanPinRenWuDAO.Add(BuMenChanPinRenWuEntity entityInfo)", "根据实体添加数据");
            }
        }
        /// <summary>
        /// 创建人：
        /// 创建时间：2010-7-12
        /// 功能介绍：添加BuMenChanPinRenWuEntity
        /// </summary>
        /// <param name="entityInfo"></param>
        /// <returns></returns>
        public bool Add(BuMenChanPinRenWuEntity entityInfo)
        {
            string strSql = GetAddSQLStrings();
            string DelSql = GetDELSQLByNianFen();
            SqlParameter[] sqlParas = GetBuMenChanPinRenWuEntityAddParas(entityInfo);
            try
            {
                DBSQLHelper.ExecuteNonQuery(conStr, CommandType.Text, DelSql, sqlParas);
                int result = DBSQLHelper.ExecuteNonQuery(conStr, CommandType.Text, strSql, sqlParas);
                if (result > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (Exception e)
            {
                throw new CustomException(e.Message, "BuMenChanPinRenWuDAO.Add(BuMenChanPinRenWuEntity entityInfo)", "根据实体添加数据");
            }
        }
        /// <summary>
        /// 创建人：
        /// 创建时间:2010-7-12
        /// 功能简介:返回Sql语句
        /// </summary>
        /// <returns></returns>
        private string GetAddSQLStrings()
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("INSERT INTO TB_BuMenChanPinRenWu( BM_ID,BM_MingCheng,CP_MingCheng,BMCP_RenWu,BMCP_RenWuNianFen,CP_LeiBie) values( @BM_ID,@BM_MingCheng,@CP_MingCheng,@BMCP_RenWu,@BMCP_RenWuNianFen,@CP_LeiBie)");
            return strSql.ToString();
        }

        /// <summary>
        /// 创建人：
        /// 创建时间:2010-7-12
        /// 功能简介:返回BuMenChanPinRenWuEntity参数赋值组
        /// </summary>
        /// <param name="entity"></param>
        /// <returns></returns>
        private SqlParameter[] GetBuMenChanPinRenWuEntityAddParas(BuMenChanPinRenWuEntity entity)
        {
            SqlParameter[] parameter = new SqlParameter[]
                {
                    //new SqlParameter("@BMCP_ID",entity.BMCP_ID),
                    new SqlParameter("@BM_ID",entity.BM_ID),
                     new SqlParameter("@BM_MingCheng",entity.BM_MingCheng),
                    new SqlParameter("@CP_MingCheng",entity.CP_MingCheng),
                    new SqlParameter("@BMCP_RenWu",entity.BMCP_RenWu),
                    new SqlParameter("@BMCP_RenWuNianFen",entity.BMCP_RenWuNianFen),
                    new SqlParameter("@CP_LeiBie",entity.CP_LeiBie)
               
                 };
            return parameter;
        }

        /// <summary>
        /// 创建人：
        /// 创建时间：2010-7-12
        /// 功能介绍：删除BuMenChanPinRenWuEntity
        /// </summary>
        /// <param name="entityInfo"></param>
        /// <returns></returns>
        public bool DelBMCP_RenWu(string NianFen,string BuMen)
        {
          
            string DelSql = GetDELSQLByNianFen();

            try
            {
                int result = DBSQLHelper.ExecuteNonQuery(conStr, CommandType.Text, DelSql, DelByNMParamts(NianFen, BuMen));
                if (result > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (Exception e)
            {
                throw new CustomException(e.Message, "BuMenChanPinRenWuDAO.DelBMCP_RenWu(string NianFen,string BuMen)", "根据年份、部门名称数据");
            }
        }

        /// <summary>
        /// 创建人：
        /// 创建时间:2010-7-12
        /// 功能简介:返回Sql语句
        /// </summary>
        /// <returns></returns>
        private string GetDELSQLByNianFen()
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from TB_BuMenChanPinRenWu where BMCP_RenWuNianFen=@BMCP_RenWuNianFen and BM_MingCheng=@BM_MingCheng");
            return strSql.ToString();
        }


        public SqlParameter[] DelByNMParamts(string NianFen,string BuMen)
        {
            SqlParameter[] paramts = new SqlParameter[] 
            {
            
            
                new SqlParameter("@BMCP_RenWuNianFen",NianFen),
                new SqlParameter("@BM_MingCheng",BuMen)
            
            };

            return paramts;
        
        }


        /// <summary>
        /// 创建人：
        /// 创建时间：2010-7-12
        /// 功能介绍：删除BuMenChanPinRenWuEntity
        /// </summary>
        /// <param name="key"></param>
        /// <returns></returns>
        public bool Delete(string key)
        {
            string strSql = GetDeleteSQLStrings();
            SqlParameter[] sqlParas = GetDeleteEntityParas(key);
            try
            {
                int result = DBSQLHelper.ExecuteNonQuery(conStr, CommandType.Text, strSql, sqlParas);
                if (result > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (Exception e)
            {
                throw new CustomException(e.Message, "BuMenChanPinRenWuDAL.Delete(key)", "根据条件删除BuMenChanPinRenWuEntity信息，同时返回删除结果");
            }
        }

        /// <summary>
        /// 创建人：
        /// 创建时间:2010-7-12
        /// 功能简介:返回Sql语句
        /// </summary>
        /// <returns></returns>
        private string GetDeleteSQLStrings()
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("DELETE FROM TB_BuMenChanPinRenWu WHERE BMCP_ID= @BMCP_ID");
            return strSql.ToString();
        }

        /// <summary>
        /// 创建人：
        /// 创建时间:2010-7-12
        /// 功能简介:返回DeleteEntity参数赋值组
        /// </summary>
        /// <param name="entity"></param>
        /// <returns></returns>
        private SqlParameter[] GetDeleteEntityParas(string key)
        {
            SqlParameter[] parameter = new SqlParameter[]
                {
                    new SqlParameter("@BMCP_ID",key)
               
                 };
            return parameter;
        }

        /// <summary>
        /// 创建人：
        /// 创建时间：2010-7-12
        /// 功能介绍：更新BuMenChanPinRenWuEntity
        /// </summary> 
        /// <param name="entityInfo"></param>
        /// <returns></returns>      
        public bool Update(BuMenChanPinRenWuEntity entityInfo)
        {
            string strSql = GetUpdateSQLStrings();
            SqlParameter[] sqlParas = GetUpdateBuMenChanPinRenWuEntity(entityInfo);
            try
            {
                int result = DBSQLHelper.ExecuteNonQuery(conStr, CommandType.Text, strSql, sqlParas);
                if (result > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (Exception e)
            {
                throw new CustomException(e.Message, "BuMenChanPinRenWuDAL.Update(BuMenChanPinRenWuEntity entityInfo)", "更新实体信息");
            }
        }
        /// <summary>
        /// 创建人：
        /// 创建时间:2010-7-12
        /// 功能简介:返回Sql语句
        /// </summary>
        /// <returns></returns>
        private string GetUpdateSQLStrings()
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("UPDATE TB_BuMenChanPinRenWu SET  BM_ID=@BM_ID,BM_MingCheng=@BM_MingCheng,CP_MingCheng=@CP_MingCheng,BMCP_RenWu=@BMCP_RenWu,BMCP_RenWuNianFen=@BMCP_RenWuNianFen,CP_LeiBie=@CP_LeiBie WHERE  1 = 1 AND BMCP_ID= @BMCP_ID");
            return strSql.ToString();
        }

        /// <summary>
        /// 创建人：
        /// 创建时间：2010-7-12
        /// 功能简介：返回BuMenChanPinRenWuEntity参数赋值
        /// </summary>
        /// <param name="entity"></param>
        /// <returns></returns>
        private SqlParameter[] GetUpdateBuMenChanPinRenWuEntity(BuMenChanPinRenWuEntity entity)
        {
            SqlParameter[] parameter = new SqlParameter[]
                {
                    new SqlParameter("@BMCP_ID",entity.BMCP_ID),
                    new SqlParameter("@BM_ID",entity.BM_ID),
                    new SqlParameter("@BM_MingCheng",entity.BM_MingCheng),
                    new SqlParameter("@CP_MingCheng",entity.CP_MingCheng),
                    new SqlParameter("@BMCP_RenWu",entity.BMCP_RenWu),
                    new SqlParameter("@BMCP_RenWuNianFen",entity.BMCP_RenWuNianFen),
                    new SqlParameter("@CP_LeiBie",entity.CP_LeiBie)
               
                 };
            return parameter;
        }

        /// <summary>
        /// 创建人：
        /// 创建时间：2010-7-12
        /// 功能介绍：根据“key”查询BuMenChanPinRenWuEntity
        /// </summary>
        /// <param name="key"></param>
        /// <returns></returns>
        public BuMenChanPinRenWuEntity GetListDetailById(string key)
        {
            DataRow row = null;
            BuMenChanPinRenWuEntity buMenChanPinRenWuEntity = new BuMenChanPinRenWuEntity();
            StringBuilder sbSqlBuilder = new StringBuilder();
            try
            {
                sbSqlBuilder.Append("SELECT * FROM TB_BuMenChanPinRenWu WHERE TB_BuMenChanPinRenWu.BMCP_ID = @BMCP_ID");
                string sqlStr = sbSqlBuilder.ToString();
                SqlParameter[] parameters = new SqlParameter[]{
                        new SqlParameter("@BMCP_ID",key)
                    };
                DataTable BuMenChanPinRenWuDT = new DataTable();
                BuMenChanPinRenWuDT = DBSQLHelper.GetDataTable(conStr, CommandType.Text, sqlStr, parameters);
                if ((BuMenChanPinRenWuDT != null) && (BuMenChanPinRenWuDT.Rows.Count > 0))
                {
                    row = BuMenChanPinRenWuDT.Rows[0];
                }
                else
                {
                    return buMenChanPinRenWuEntity;
                }
            }
            catch (Exception e)
            {
                throw new CustomException(e.Message, "BuMenChanPinRenWuDAL.GetListDetailById(string key)", "获得BuMenChanPinRenWu详细信息");
            }
            buMenChanPinRenWuEntity.BMCP_ID = DataSwitch.ConvertObjectToInt32(row["BMCP_ID"]);
            buMenChanPinRenWuEntity.BM_ID = DataSwitch.ConvertObjectToString(row["BM_ID"]);
            buMenChanPinRenWuEntity.BM_MingCheng = DataSwitch.ConvertObjectToString(row["BM_MingCheng"]);
            buMenChanPinRenWuEntity.CP_MingCheng = DataSwitch.ConvertObjectToString(row["CP_MingCheng"]);
            buMenChanPinRenWuEntity.BMCP_RenWu = DataSwitch.ConvertObjectToDecimal(row["BMCP_RenWu"]);
            buMenChanPinRenWuEntity.BMCP_RenWuNianFen = DataSwitch.ConvertObjectToString(row["BMCP_RenWuNianFen"]);
            buMenChanPinRenWuEntity.CP_LeiBie = DataSwitch.ConvertObjectToString(row["CP_LeiBie"]);

            return buMenChanPinRenWuEntity;

        }

        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public DataTable GetAllDeptForYouXiao()
        {
            string strSql = "select * from TB_BuMen where BM_ShiFouYouXiao = 1";
            try
            {
                return DBSQLHelper.GetDataTable(conStr, CommandType.Text, strSql, null);
            }
            catch (Exception e)
            {
                throw new CustomException(e.Message, "BuMenChanPinRenWuDAL.GetAllDeptForYouXiao()", "获取有效的部门信息");
            }
        }
        /// <summary>
        /// 获取部门产品信息
        /// </summary>
        /// <param name="BM_ID"></param>
        /// <returns></returns>
        public DataTable GetBuMenChanPinXinXi()
        {
            try
            {
                return DBSQLHelper.GetDataTable(conStr, CommandType.StoredProcedure, "proGetProduct", null);
            }
            catch (Exception e)
            {
                throw new CustomException(e.Message, "BuMenChanPinRenWuDAL.GetBuMenChanPinXinXi(string BM_ID)", "获取部门产品信息");
            }
        }


        public DataTable GetBuMenChanPinRenWuBY_NianFen(string NianFen, string BuMen)
        {
            try
            {
            return DBSQLHelper.GetDataTable(conStr, CommandType.Text, BuMenByNianFenSQL(),GetBuMenRenWuBYNianFen(NianFen,BuMen));
        }
        catch (Exception e)
        {
            throw new CustomException(e.Message, "BuMenChanPinRenWuDAL.GetBuMenChanPinRenWuBY_NianFen(string NianFen, string BuMen)", "根据年份、部门获取部门产品任务信息");
        }
        }

        public string BuMenByNianFenSQL()
        {

            string SQL = "select * from TB_BuMenChanPinRenWu where BM_MingCheng=@BM_MingCheng and BMCP_RenWuNianFen=@BMCP_RenWuNianFen";

            return SQL;
        }

        private SqlParameter[] GetBuMenRenWuBYNianFen(string NianFen,string BuMen)
        {

            SqlParameter[] paramts = new SqlParameter[] 
            {
            
                new SqlParameter("@BM_MingCheng",BuMen),
                new SqlParameter("@BMCP_RenWuNianFen",NianFen)
            };

            return paramts;
        }
    }
}
